use std::{char::from_u32, collections::HashMap};

use model_graph_types::{
    connector::{Connector, ExecuteOptions, QueryOptions},
    database::DataSource,
    modeling::Value,
};
use sqlx::types::chrono;
use sqlx::{
    sqlite::{SqliteArguments, SqliteColumn, SqlitePool, SqliteRow},
    Arguments, Column, Error, Row, TypeInfo, ValueRef,
};

pub struct SqliteConnector {
    pool: SqlitePool,
}

impl SqliteConnector {
    pub async fn open(data_source: &DataSource) -> anyhow::Result<SqliteConnector> {
        let pool = SqlitePool::connect(&data_source.url).await?;
        let connector = SqliteConnector { pool };
        Ok(connector)
    }
}

fn sqlite_row_2_value(row: &SqliteRow) -> anyhow::Result<Value> {
    //
    let mut values = vec![];

    for i in 0..row.len() {
        let column = row.column(i);
        let c_type = column.type_info().name();

        let mut value = _to_value_by_sqlite_type(column, c_type, i, row)?;
        match value {
            Value::None => {
                //直接得到值
                let v = row.try_get_raw(i)?;
                let c_type = v.type_info();
                value = _to_value_by_sqlite_type(column, c_type.name(), i, row)?;
            }
            _ => {}
        }
        match value {
            Value::None => {
                tracing::debug!("{:?},type:[{:?}]", column, c_type);
            }
            _ => {}
        }
        values.push(value);
    }
    Ok(Value::Array(values))
}

fn _to_value_by_sqlite_type(
    column: &SqliteColumn,
    c_type: &str,
    i: usize,
    row: &SqliteRow,
) -> anyhow::Result<Value> {
    let result = _to_value_by_sqlite_type_r(column, c_type, i, row);

    match result {
        Ok(v) => Ok(v),
        Err(err) => match err {
            Error::ColumnDecode { index, source } => {
                tracing::debug!("sqlit值转换失败:[{}]{:?}", index, source);
                Ok(Value::None)
            }
            _ => {
                tracing::debug!("sqlit值转换失败:{}", err);
                Err(anyhow::anyhow!("{}", err))
            }
        },
    }
}

fn _to_value_by_sqlite_type_r(
    column: &SqliteColumn,
    c_type: &str,
    i: usize,
    row: &SqliteRow,
) -> Result<Value, Error> {
    let value = match c_type.to_uppercase().as_str() {
        "NULL" => Value::None,
        "TEXT" => Value::String(row.try_get::<String, usize>(i)?),
        "REAL" => Value::Float(row.try_get::<f64, usize>(i)?),
        "INTEGER" => Value::Number(row.try_get::<i64, usize>(i)?),
        "DATETIME" => {
            let v = row.try_get::<chrono::DateTime<chrono::Utc>, usize>(i)?;
            Value::Timestamp(v.timestamp_millis())
        }
        _ => Value::None,
    };
    Ok(value)
}

fn sqlite_rows_2_value(rows: &Vec<SqliteRow>) -> anyhow::Result<Value> {
    //
    let mut values = vec![];

    for row in rows {
        values.push(sqlite_row_2_value(row)?);
    }

    Ok(Value::Array(values))
}

#[async_trait::async_trait]
impl Connector for SqliteConnector {
    async fn query(
        &self,
        sql: &String,
        args: &Vec<Value>,
        options: QueryOptions,
    ) -> anyhow::Result<(Vec<Value>, Vec<Value>)> {
        let rows = if args.len() > 0 {
            //添加参数
            sqlx::query_with(sql, into_arguments(&args))
                .fetch_all(&self.pool)
                .await?
        } else {
            sqlx::query(sql).fetch_all(&self.pool).await?
        };

        //get headers
        let mut headers = vec![];
        if rows.len() > 0 {
            //
            let columns = rows
                .get(0)
                .map_or(Err(anyhow::anyhow!("")), |v| Ok(v))?
                .columns();
            headers = _get_columns(columns)?;
        }

        let mut values = vec![];
        for row in rows {
            values.push(sqlite_row_2_value(&row)?);
        }

        Ok((headers, values))
    }

    async fn query_one(
        &self,
        sql: &String,
        args: &Vec<Value>,
        options: QueryOptions,
    ) -> anyhow::Result<(Vec<Value>, Value)> {
        let row = if args.len() > 0 {
            //添加参数
            sqlx::query_with(sql, into_arguments(&args))
                .fetch_one(&self.pool)
                .await?
        } else {
            sqlx::query(sql).fetch_one(&self.pool).await?
        };
        //get headers
        let headers = _get_columns(row.columns())?;

        Ok((headers, sqlite_row_2_value(&row)?))
    }

    async fn query_map(
        &self,
        sql: &String,
        args: &Vec<Value>,
        options: QueryOptions,
    ) -> anyhow::Result<HashMap<String, Value>> {
        if let (headers, Value::Array(data)) = self.query_one(sql, args, options).await? {
            Ok(_to_object(&headers, data)?)
        } else {
            Err(anyhow::anyhow!("查询失败"))
        }
    }

    async fn query_maps(
        &self,
        sql: &String,
        args: &Vec<Value>,
        options: QueryOptions,
    ) -> anyhow::Result<Vec<HashMap<String, Value>>> {
        if let (headers, data) = self.query(sql, args, options).await? {
            let mut res = vec![];
            for row in data {
                match row {
                    Value::Array(v) => res.push(_to_object(&headers, v)?),
                    _ => {}
                }
            }
            Ok(res)
        } else {
            Err(anyhow::anyhow!("查询失败"))
        }
    }

    async fn execute(
        &self,
        sql: &String,
        args: &Vec<Value>,
        options: ExecuteOptions,
    ) -> anyhow::Result<i64> {
        let mut conn = self.pool.acquire().await?;

        let res = if args.len() > 0 {
            //添加参数
            sqlx::query_with(sql, into_arguments(&args))
                .execute(&mut *conn)
                .await?
        } else {
            sqlx::query(sql).execute(&mut *conn).await?
        };

        if res.last_insert_rowid() > 0 {
            Ok(res.last_insert_rowid() as i64)
        } else {
            Ok(i64::try_from(res.rows_affected())?)
        }
    }

    async fn close(&mut self) -> anyhow::Result<i32> {
        Ok(0)
    }
}

pub fn into_arguments(args: &Vec<Value>) -> SqliteArguments {
    let mut arguments = SqliteArguments::default();

    for arg in args {
        let v = match arg {
            Value::String(str) => arguments.add(str),
            Value::Char(str) => arguments.add(str.to_string()),
            Value::Timestamp(str) => {
                arguments.add(chrono::DateTime::<chrono::Utc>::from_timestamp_millis(*str))
            }
            Value::DateTime(str) => {
                arguments.add(chrono::DateTime::<chrono::Utc>::from_timestamp_millis(*str))
            }
            Value::Boolean(v) => arguments.add(if *v { 1 } else { 0 }),
            Value::Number(v) => arguments.add(v.clone()),
            Value::Float(v) => arguments.add(v.clone() as f64),
            Value::Bytes(v) => arguments.add(v),
            _ => arguments.add(Option::<String>::None),
        };
    }

    arguments
}

fn _get_columns(columns: &[SqliteColumn]) -> anyhow::Result<Vec<Value>> {
    let mut headers = vec![];

    for column in columns {
        let mut obj = HashMap::<String, Value>::new();
        obj.insert(
            String::from("name"),
            Value::String(String::from(column.name())),
        );
        obj.insert(
            String::from("label"),
            Value::String(String::from(column.name())),
        );
        obj.insert(
            String::from("type"),
            Value::String(String::from(column.type_info().name())),
        );
        obj.insert(
            String::from("ordinal"),
            Value::Number(column.ordinal() as i64),
        );

        headers.push(Value::Object(obj));
    }
    Ok(headers)
}

fn _to_object(headers: &Vec<Value>, data: Vec<Value>) -> anyhow::Result<HashMap<String, Value>> {
    let mut object = HashMap::new();

    for i in 0..headers.len() {
        if let Some(header) = headers.get(i) {
            match header {
                Value::Object(hm) => {
                    if let Some(name) = hm.get(&String::from("name")) {
                        match name {
                            Value::String(hv) => {
                                if let Some(value) = data.get(i) {
                                    object.insert(hv.clone(), value.clone());
                                }
                            }
                            _ => {}
                        }
                    }
                }
                Value::String(hv) => {
                    if let Some(value) = data.get(i) {
                        object.insert(hv.clone(), value.clone());
                    }
                }
                _ => {}
            }
        }
    }
    Ok(object)
}
